{S:X;X;0;0;Using the Set_value(,) Function{ITR-X}by Gerald L Fitton}
{S:X;X;0;1;Keywords:{ITT-X}Set_value Fitton}
{S:X;X;0;2;Introduction{ITR-X}{ITR-X}I have received a wide range of correspondence about the set_value(,) function. This correspondence has ranged from the simplest \“Please give us a simple example of how it can be used and explain what it can used for\” to \“I\’m having a great deal of trouble with set_value(,), please help me!\”}
{S:X;X;0;3;As a result I have decided to include two SetValue directories on this disc. This, the first contains a simple example of the use of the set_value(,) function. The second, is an excellent example\—because of its simplicity\—which demonstrates a \‘bug\’ which some of you have found. More often, you have not found (or worse, not even noticed) the \‘bug\’ and the results you have been getting are making you wonder whether you are using set_value(,) (or some other function in your spreadsheet) incorrectly.}
{S:X;X;0;4;As a result of your correspondence I have decided to include two SetValue directories on this disc. This, the first, is a simple example of the use of the set_value(,) function. The second is an excellent example (because of its simplicity) which demonstrates a \‘bug\’ which many of you have found\—or, more often, not found and is making you wonder whether you are using set_value(,) correctly.}
{S:X;X;0;5;A Warning{ITR-X}{ITR-X}First my usual warning! Don\’t use the original files but make a copy and work with the copies. What I do with files sent to me by contributors is to use a [ScrapIt] directory which I have set up on my hard disc specially for the purpose. I copy the files you send me to that [ScrapIt] directory, work on them and then, finally, copy them to a more permanent home or scrap them. As an alternative I sometimes I use a RAM disc as a temporary \‘home\’\\\; this is a useful tactic when I have huge files needing quick acces.}
{S:X;X;0;6;An Example of set_value(,){ITR-X}{ITR-X}Let\’s start with an example of it\’s use. Double click on the file [Input] (in this directory) and you will find that it and two other files, [Letter] and [Data] will also load. The file [Input] should be \‘in front\’ and the caret should be in cell [Input]a2. In it you will find my name, Gerald L Fitton. You will also find my name and our Abacus Training address appears in the block of cells [Letter]a4a10.}
{S:X;X;0;7;Mark the contents of the cell [Input]a2 by dragging from the G of Gerald to the n of Fitton. Do not triple click on Gerald L Fitton or you will mark not only the Gerald L Fitton but also the <Return> after it. This <Return> is a paragraph marker. Delete the marked block either with the <Delete> key or by clicking on the Delete button (which looks like a pair of scissors) on the button bar. If, by mistake, you had include the <Return> in your marked block then you would find that row 2 disappears completely moving rows 4 and 5 up one row!}
{S:X;X;0;8;Notice that the whole of the address in the block [Letter]a4a10 has vanished!}
{S:X;X;0;9;In the (now) blank cell, Input]a2, type Mark Colton. Do not tap <Return> but move the cursor out of the cell either with the mouse or with the up or down arrow keys. The name Mark Colton and his address will appear in the address block, [Letter]a4a10.}
{S:X;X;0;10;This next bit is important if you are not going to corrupt the file [Input]. Remove the file [Input] from the screen\\\; use the Close doc option and leave all the the other files in the group on the screen. Write the rest of your letter and Save it under a name other than [Letter] such as [Letter2].}
{S:X;X;0;11;An alternative to the technique of the last paragraph is to leave [Input] where it is, Save [Letter] and then, use the RISC OS filing system to make a copy of your [Letter] under another name such as [Letter2]. Reload the copy of the letter, [Letter2], complete writing it and then Save it.}
{S:X;X;0;12;If you Save [Letter] as [Letter2] with [Input] still loaded then you will \‘corrupt\’ the file [Input]. References in it to [Letter] will change to references to [Letter2]\—and you don\’t want that!}
{S:X;X;0;13;How does it work{ITR-X}{ITR-X}There are formulae in cells [Input]a4, Data[c3h3] and [Input]a5 and nowhere else. In particular, there are no formulae in the file [Letter]. Let us deal with them in that order.}
{S:X;X;0;14;Push or Pull{ITR-X}{ITR-X}The formula in [Input]a4 is set_value([Data]b3,a2). This formula \‘pushes\’ the value in [Input]a2 into the slot [Data]b3 ([Data] is in the database document). In a circumstance like this I prefer to \‘push\’ the input data from the input document rather than use a set_value(,) in the database document which \‘pulls\’ the input data into the database. The reason in this case is that I can load the database independently of the letter writing application and work on it. For example I can add new rows of records, without the complication and delay of having other documents loaded which are dependent of the database. Perhaps more interestingly this technique means that I can use the database for more than one application, for example I could use it for invoicing by setting up a separate [Invoice] file which might have as input an invoice number rather than a name.}
{S:X;X;0;15;The Master Row{ITR-X}{ITR-X}Have a look now at the database file [Data]. The only formulae are in [Data]c3h3 and these are lookup(,,) functions. I constructed these functions by typing the function in full in slot [Data]c3 and then copying the formula across the block [Data]c3h3.}
{S:X;X;0;16;The formula in [Data]c3 is lookup($b$3,$b$8$12,c8c12). You can ignore the $ signs for the moment\\\; lookup($b$3,$b$8$12,c8c12) acts exactly like lookup(b3,b812,c8c12). What it does is to find the contents of b3 (Gerald L Fitton) somewhere in the column b8b12. Of course it does so on row 9. The value returned to the slot c3 is found in row 9 of column c.}
{S:X;X;0;17;You will notice that, in the lookup(,,) function, lookup($b$3,$b$8$b$12,c8c12), I have $ signs in the first two arguments but no $ signs in the third argument. The effect of the $ signs is to ensure that as I copy the formula from left to right that these two arguments do not change. I don\’t want them to change because I want to look up Gerald L Fitton (which is in [Data]b3) in the block [Data\}b8b12 for all six of my lookup(,,) functions. By omitting the $ signs from the third argument I ensure that the value returned is always returned from the \‘local\’ correct column.}
{S:X;X;0;18;Of course, if [Input] is not loaded then you can type Mark Colton into [Data]b3 and you will find that the lookup(,,) functions return the rest of the address in the Master row, row 3. If you delete everything from the cell [Data]b3 then you will find that the lookup(,,) functions also return blank slots. Why? Because the \‘key\’, a blank, is found in row 8!}
{S:X;X;0;19;Pushing the Address{ITR-X}{ITR-X}Finally we return to the [Input] file and cell [Input]a5. where we find the last formula which is set_value([Letter]a4a10,transpose([Data]b3h3)). This is another use of the set_value(,,) function which \‘pushes\’ the address from the row [Data]b3h3 into the column [Letter]a4a1. The advantage of \‘pushing\’ in [Input] here rather than \‘pulling\’ from [Letter] is that once the address has been \‘pushed\’ into it the [Letter] document can stand alone. By this I mean that you can delete [Input] from the screen and [Letter] will not give you error messages of the type \“File not loaded\”. [Letter] doesn\’t even need [Data].}
{S:X;X;0;20;If you remove [Input] from the screen then you will have \‘unhooked\’ the [Letter] file from its database, [Data]. The two files are now independent! You can Save [Letter] as, for example, [Letter2] without corruption. Beware that if you don\’t remove [Input] from the screen before you Save [Letter] as [Letter2] then all references to [Letter] in the [Input] document will change to [Letter2] and you don\’t want that!}
{S:X;X;0;21;Finally{ITR-X}{ITR-X}Please let me know if you have found this tutorial helpful. Please let me know if you would like more examples of this useful function, or any other function for that matter.}
